﻿using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;


namespace Net_SCADA
{
    class DBBase
    {
        #region BaseFunctions
        public static string dbName { get; set; } = "netscadawzxc2";
        public static string dbUser { get; set; } = "root";
        public static string dbPwd { get; set; } = "FengDong";
        public static string dbServer { get; set; } = "Localhost";
        public static string dbPort { get; set; } = "3306";

        public static void LoadDBSet()
        {
            Ini dbIni = new Ini(System.Windows.Forms.Application.StartupPath + @"\settings\db.ini");

            string v = dbIni.ReadValue("DBbase", "dbServer").Trim();
            if (!string.IsNullOrEmpty(v)) dbServer = v;

            v = dbIni.ReadValue("DBbase", "dnUser").Trim();
            if (!string.IsNullOrEmpty(v)) dbUser = v;

            v = dbIni.ReadValue("DBbase", "dbPwd");
            if (!string.IsNullOrEmpty(v)) dbPwd = v;

            v = dbIni.ReadValue("DBbase", "dbName").Trim();
            if (!string.IsNullOrEmpty(v)) dbName = v;

            v = dbIni.ReadValue("DBbase", "dbPort").Trim();
            if (!string.IsNullOrEmpty(v)) dbPort = v;
        }
        public static bool CheckTable(string table)
        {
            DataTable dt=GetDataTable(@"show tables like '"+ table +"'");
            if (dt != null && dt.Rows.Count > 0)
                return true;
            return false;
        }
        public static StringBuilder CreateSQLCommand(string table , Dictionary<string , string> v)
        {
            StringBuilder sb_cmd = new StringBuilder("INSERT INTO `" + table + "` (`SaveTime`,");
            StringBuilder sb_val = new StringBuilder(")VALUES('" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "',");
            foreach (string item in v.Keys)
            {
                sb_cmd.Append("`").Append(item).Append("`,");
                sb_val.Append("'").Append(v[item]).Append("',");
            }
            sb_cmd = new StringBuilder(sb_cmd.ToString(0 , sb_cmd.Length - 1));
            sb_cmd.Append(sb_val.ToString(0 , sb_val.Length - 1)).Append(");");
            return sb_cmd;
        }

        public static StringBuilder CreateSQLCommand(string table,Dictionary<int, double> v)
        {
            StringBuilder sb_cmd = new StringBuilder("INSERT INTO `" + table + "` (`SaveTime`,");
            StringBuilder sb_val = new StringBuilder(")VALUES('" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "',");
            foreach (int item in v.Keys)
            {
                sb_cmd.Append("`").Append(item).Append("`,");
                sb_val.Append("'").Append(v[item]).Append("',");
            }
            sb_cmd = new StringBuilder(sb_cmd.ToString(0 , sb_cmd.Length - 1));
            sb_cmd.Append(sb_val.ToString(0 , sb_val.Length - 1)).Append(");");
            return sb_cmd;
        }
        public static DataTable GetDataTable(string sqlcommand)
        {
            DataTable dt = new DataTable();
            if (sqlcommand.Length == 0) return dt;
            MySqlConnection conn = ConnetDB();
            MySqlCommand cmd = new MySqlCommand(sqlcommand, conn);
            try
            {
                MySqlDataAdapter adapter = new MySqlDataAdapter(cmd.CommandText, conn);
                adapter.Fill(dt);
            }
            catch (Exception) {}
            return dt;
        }

        public static bool CreateDateBase()
        {
            try
            {
                MySqlConnection conn = new MySqlConnection("Data Source="+ dbServer + ";Persist Security Info=yes;User ID="+dbUser+";Password="+dbPwd+";port="+ dbPort+";");
                MySqlCommand cmd = new MySqlCommand("CREATE DATABASE IF NOT EXISTS "+dbName+" default charset utf8 COLLATE utf8_general_ci;", conn);
                conn.Open();
                cmd.ExecuteNonQuery();
                conn.Close();
                return true;
            }
            catch (Exception)
            {
                return false;
            }
        }
        public static bool CreateTable(String SQLCommand)
        {
            MySqlConnection conn =ConnetDB();
            try
            {
                if (conn.State == ConnectionState.Open) conn.Close();
                conn.Open();
                MySqlCommand myCommand = new MySqlCommand(SQLCommand, conn);
                myCommand.ExecuteNonQuery();
                conn.Close();
                return true;
            }
            catch (Exception e)
            {
                throw;
                return false;
            }
        }
        public static bool ExecuteSqlTran(List<string> SQLList)
        {
            using (MySqlConnection conn = ConnetDB())
            {
                conn.Open();
                MySqlCommand cmd = new MySqlCommand();
                cmd.Connection = conn;
                MySqlTransaction tx = conn.BeginTransaction();
                cmd.Transaction = tx;
                try
                {
                    for (int n = 0 ; n < SQLList.Count ; n++)
                    {
                        if (SQLList[n].Trim().Length > 1)
                        {
                            cmd.CommandText = SQLList[n];
                            cmd.ExecuteNonQuery();
                        }
                        if (n >= 0 && (n % 500 == 0 || n == SQLList.Count - 1))
                        {
                            tx.Commit();
                            tx = conn.BeginTransaction();
                        }
                    }
                    return true;
                }
                catch (System.Data.SqlClient.SqlException E)
                {
                    tx.Rollback();
                    return false;
                }
            }
        }
        public static MySqlConnection ConnetDB()
        {
            //string ConString = "Server="+ dbServer + ";Database="+dbName+"; User ID="+dbUser+";Password="+dbPwd+";port="+dbport+";CharSet=utf8;pooling=true;SslMode=None;";
            string ConString = "Database=" + dbName + ";Data Source=" + dbServer + "; User Id=" + dbUser + "; Password=" + dbPwd + "; pooling=false; CharSet=utf8; port="+dbPort;

            return new MySqlConnection(ConString);//连接数据库  
        }
        #endregion
    }
}
